package dao;

import models.ArticleInfo;
import models.vo.ArticleInfoVO;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ArticleInfoDao {

    //使用uid获取文章
    public List<ArticleInfo> getListByUID (int uid) throws SQLException {
        List<ArticleInfo> list = new ArrayList<>();
        Connection connection = DBUtils.getConnection();
        String sql = "select * from articleinfo where uid = ?"; //查询文章表
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,uid);
        //开始查询数据并返回结果
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            ArticleInfo articleInfo = new ArticleInfo(); //获得ArticleInfo类对象
            articleInfo.setId(resultSet.getInt("id"));
            articleInfo.setRcount(resultSet.getInt("rcount"));
            articleInfo.setTitle(resultSet.getString("title"));
            articleInfo.setContent(resultSet.getString("content"));
            articleInfo.setCreate_time(resultSet.getDate("create_time"));
            list.add(articleInfo);
        }
        return list;  //返回list
    };

    //删除文章
    public int del(int id) throws SQLException {
        int result = 0;
        if(id > 0){
            Connection connection = DBUtils.getConnection();
            String sql = "delete from articleinfo where id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //初始化占位符
            preparedStatement.setInt(1,id);
            //执行数据库的删除
            result = preparedStatement.executeUpdate();
            //关闭数据库
            DBUtils.close(connection,preparedStatement,null);
        }
        return result;
    }

    //根据文章id查询文章详情
    public ArticleInfoVO getArticleById(int id) throws SQLException {
        ArticleInfoVO articleInfoVO = new ArticleInfoVO();
        if(id > 0){
            Connection connection = DBUtils.getConnection();
            String sql = "select a.*,u.username from articleinfo a left join userinfo u on a.uid=u.id where a.id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                articleInfoVO.setUsername(resultSet.getString("username"));
                articleInfoVO.setCreate_time(resultSet.getDate("create_time"));
                articleInfoVO.setId(resultSet.getInt("id"));
                articleInfoVO.setTitle(resultSet.getString("title"));
                articleInfoVO.setContent(resultSet.getString("content"));
                articleInfoVO.setRcount(resultSet.getInt("rcount"));
            }
            //关闭数据库连接
            DBUtils.close(connection,preparedStatement,resultSet);
        }
        return articleInfoVO;
    }

    //文章修改
    public int updateArticle(int id, String title, String content) throws SQLException {
        int result = 0;
        Connection connection = DBUtils.getConnection();
        String sql = "update articleinfo set title = ?,content = ? where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,title);
        preparedStatement.setString(2,content);
        preparedStatement.setInt(3,id);
        result = preparedStatement.executeUpdate();
        DBUtils.close(connection,preparedStatement,null);
        return result;
    }

    //添加文章
    public int addArticle(String title, String content, int uid) throws SQLException {
        int result = 0;
        Connection connection = DBUtils.getConnection();
        String sql = "insert into articleinfo(title,content,uid) values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,title);
        preparedStatement.setString(2,content);
        preparedStatement.setInt(3,uid);
        result = preparedStatement.executeUpdate();
        DBUtils.close(connection,preparedStatement,null);
        return result;
    }

    // 查询所有人的文章
    public List<ArticleInfoVO> getList() throws SQLException {
        List<ArticleInfoVO> list = new ArrayList<>();
        Connection connection = DBUtils.getConnection();
        String sql = "select a.*,u.username from articleinfo a left join userinfo u on a.uid=u.id";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            ArticleInfoVO vo = new ArticleInfoVO();
            vo.setId(resultSet.getInt("id"));
            vo.setUsername(resultSet.getString("username"));
            vo.setTitle(resultSet.getString("title"));
            vo.setRcount(resultSet.getInt("rcount"));
            vo.setCreate_time(resultSet.getDate("create_time"));
            list.add(vo);
        }
        DBUtils.close(connection, statement, resultSet);
        return list;
    }

    //分页查询所有人文章
    public List<ArticleInfoVO> getListByPage(int cpage, int psize) throws SQLException {
        List<ArticleInfoVO> list = new ArrayList<>();
        Connection connection = DBUtils.getConnection();
        String sql = "select a.*,u.username from articleinfo a left join userinfo u on a.uid=u.id limit ?,?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,(cpage-1)*psize);
        statement.setInt(2,psize);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            ArticleInfoVO vo = new ArticleInfoVO();
            vo.setId(resultSet.getInt("id"));
            vo.setUsername(resultSet.getString("username"));
            vo.setTitle(resultSet.getString("title"));
            vo.setRcount(resultSet.getInt("rcount"));
            vo.setCreate_time(resultSet.getDate("create_time"));
            list.add(vo);
        }
        DBUtils.close(connection, statement, resultSet);
        return list;
    }

    // 阅读量+1
    public int upRcount(int id) throws SQLException {
        int result = 0;
        Connection connection = DBUtils.getConnection();
        String sql = "update articleinfo set rcount = rcount+1 where id=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        result = statement.executeUpdate();
        return result;
    }
}
